﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Sockets;
using System.Text;
using Emoney.DataAccess;
using LiuYan.Base;
using LiuYan.Entity;


namespace LiuYan.Service
{
    public class MenuService
    {
        /// <summary>
        /// 编辑菜单
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Edit(TB_Menu entity)
        {
            SqlParameter[] parameters=new SqlParameter[]
            {
                new SqlParameter("@Id",SqlDbType.BigInt),
                new SqlParameter("@Name",SqlDbType.VarChar,50),
                new SqlParameter("@Url",SqlDbType.VarChar,150),
                new SqlParameter("@IsLanMu",SqlDbType.BigInt),
                new SqlParameter("@UserId",SqlDbType.BigInt),
                new SqlParameter("@Pid",SqlDbType.BigInt),
                new SqlParameter("@Date",SqlDbType.DateTime),
                new SqlParameter("@Remark",SqlDbType.VarChar,200),
                new SqlParameter("@Order",SqlDbType.Int),
            };
            parameters[0].Value = entity.Id;
            parameters[1].Value = entity.Name;
            parameters[2].Value = entity.Url;
            parameters[3].Value = entity.IsLanMu;
            parameters[4].Value = entity.UserId;
            parameters[5].Value = entity.Pid;
            parameters[6].Value = entity.Date;
            parameters[7].Value = entity.Remark;
            parameters[8].Value = entity.Order;
            string sql =entity.Id==0?
                "insert into dbo.TB_Menu(Name,Url,IsLanMu,Del,UserId,Pid,date,Remark,[Order])values(@Name,@Url,@IsLanMu,0,@UserId,@Pid,@Date,@Remark,@Order)"
                : "update dbo.TB_Menu set Name=@Name,Url=@Url,IsLanMu=@IsLanMu,UserId=@UserId,Pid=@Pid,[Order]=@Order,Remark=@Remark where Id=@Id";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql,parameters);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetList()
        {
            string sql = "select * from dbo.TB_Menu where Del=0 and IsLanMu=0 and Pid=0    select * from dbo.TB_Menu where Del=0 and IsLanMu=0 and Pid>0";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql);
        }

        /// <summary>
        ///按序号获取父级菜单
        /// </summary>
        /// <returns></returns>
        public DataTable GetPmenu()
        {
            string sql = "select  * from dbo.TB_Menu where Del=0 and Pid=0 order by [order] asc";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }
        /// <summary>
        ///获取子级菜单
        /// </summary>
        /// <returns></returns>
        public DataTable GetCmenu(int pid)
        {
            string sql = "select  * from dbo.TB_Menu where Del=0 and Pid="+pid+" order by [order] asc";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }
        /// <summary>
        ///按序号获取父级菜单(权限菜单)
        /// </summary>
        /// <returns></returns>
        public DataTable GetPmenu(int roleId)
        {
            string sql = "select b.Id,b.Name,b.Url,b.Pid from dbo.TB_UserRole as a,dbo.TB_Menu as b where a.MenuId=b.Id and b.Del=0 and a.Del=0 and a.RoleId=" + roleId + " and b.Pid=0 order by [order] asc";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }
        /// <summary>
        ///获取子级菜单（权限菜单）
        /// </summary>
        /// <returns></returns>
        public DataTable GetCmenu(int roleId, int pid)
        {
            string sql = "select b.Id,b.Name,b.Url,b.Pid from dbo.TB_UserRole as a,dbo.TB_Menu as b where a.MenuId=b.Id and b.Del=0 and a.Del=0 and a.RoleId=" + roleId + " and b.Pid=" + pid + " order by [order] asc";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }
        /// <summary>
        ///获取子级菜单(没有父级菜单时)（权限菜单）
        /// </summary>
        /// <returns></returns>
        public DataTable GetCmenu_(int roleId)
        {
            string sql = "select b.Id,b.Name,b.Url,b.Pid from dbo.TB_UserRole as a,dbo.TB_Menu as b where a.MenuId=b.Id and b.Del=0 and a.Del=0 and a.RoleId=4 order by [order] asc";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }
        /// <summary>
        /// 获取所有列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllList(int roleid)
        {
            string sql = "select a.Id,a.Name,a.Pid,a.Url,a.Remark,a.Date,(select id from TB_UserRole where TB_UserRole.MenuId=a.Id and TB_UserRole.RoleId=" + roleid + ") as rid from dbo.TB_Menu as a where Del=0 and  Pid=0 " +
"select a.Id,a.Name,a.Pid,a.Url,a.Remark,a.Date,(select id from TB_UserRole where TB_UserRole.MenuId=a.Id and TB_UserRole.RoleId=" + roleid + ") as rid from dbo.TB_Menu as a where Del=0 and  Pid>0";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql);
        }
        /// <summary>
        /// 获取权限菜单
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        //public DataSet GetRoleMenu(int roleId)
        //{
        //    string sql = "select b.Id,b.Name,b.Url,b.Pid from dbo.TB_UserRole as a,dbo.TB_Menu as b where a.MenuId=b.Id and b.Del=0 and a.Del=0 and a.RoleId="+roleId+" and b.Pid=0"+
        //                 "select b.Id,b.Name,b.Url,b.Pid from dbo.TB_UserRole as a,dbo.TB_Menu as b where a.MenuId=b.Id and b.Del=0 and a.Del=0 and a.RoleId=" + roleId + " and b.Pid!=0";
        //    return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql);
        //}

        /// <summary>
        /// 删除菜单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Del(int id)
        {
            string sql = "select COUNT(*) count from dbo.TB_Menu where Pid="+id;
            if (Convert.ToInt32(SqlHelper.ExecuteScalar(Connection.conStr, CommandType.Text, sql))>0)
            {
                return -1;
            }
            sql = "delete TB_UserRole where MenuId="+id+" delete TB_Menu where id=" + id ;
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetPList()
        {
            string sql = "select Id,Name from TB_Menu where pid=0 and del=0";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }
        /// <summary>
        /// 获取栏目
        /// </summary>
        /// <returns></returns>
        public DataTable GetLmList()
        {
            string sql = "select Id,Name from TB_Menu where IsLanMu=1 and del=0";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }

        /// <summary>
        /// 获取菜单名称
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetName(int id)
        {
            string sql = "select Name from TB_Menu where id="+id;
            return SqlHelper.ExecuteScalar(Connection.conStr, CommandType.Text, sql).ToString();
        }

        /// <summary>
        /// 获取菜单列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetMenuList()
        {
            string sql = "select Id,Name from TB_Menu where del=0";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }
    }
}
